package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.dao.MD5;
import com.lmonkey.shop.PHOTO;
import com.lmonkey.shop.USER;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class USERDao{
    public static ArrayList<USER> selectALL(int cpage ,int count,String keyword){
        ArrayList<USER> list = new ArrayList<USER>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try{
            String sql = null;
            //limit 通过哪开始，查询多少个
            if (keyword != null){
                sql = "select * from lmonkeyshop where user_name  like ? or user_id like ? order by user_birthday desc  limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,"%"+keyword+"%");
                ps.setString(2,"%"+keyword+"%");
                ps.setInt(3,(cpage-1)*count);//从哪开始
                ps.setInt(4,count);//查询多少个
            }else {
                sql = "select * from lmonkeyshop order by user_birthday desc  limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, (cpage - 1) * count);//从哪开始
                ps.setInt(2, count);//查询多少个
            }
            rs = ps.executeQuery();
            //获取用户信息
            while (rs.next()) {
                USER u = new USER(
                        rs.getString("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_pass"),
                        rs.getString("user_sex"),
                        rs.getString("user_birthday"),
                        rs.getString("user_idenity_code"),
                        rs.getString("user_email"),
                        rs.getString("user_mobile"),
                        rs.getString("user_address"),
                        rs.getInt("user_status")
                );
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    //获取总页数以及做记录数反馈给DoUserSelect页
    //count每页显示多少数据
    public static int[] totalPage(int count,String keyword){
        //0总记录数，1总页数
        int arr[] = {0,1};
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = null;
            if (keyword != null){
                sql = "select count(*) from lmonkeyshop where user_name like ? or user_id like ?";
                ps = conn.prepareStatement(sql);
                ps.setString(1,"%"+keyword+"%");
                ps.setString(2,"%"+keyword+"%");
            }else{
                sql = "select count(*) from lmonkeyshop";
                ps = conn.prepareStatement(sql);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                arr[0] = rs.getInt(1);
            }
            if (arr[0]%count==0){
                arr[1] = arr[0]/count;
            }else{
                arr[1] = arr[0]/count+1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arr;
    }
    //通过id查询用户数据
    public static USER selectById(String id){
        USER u = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        //声明结果集
        ResultSet rs = null;

        PreparedStatement ps = null;
        try{
            String sql = "select m.*, DATE_FORMAT(m.user_birthday,'%Y-%m-%d') user_birthday1 from lmonkeyshop m where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);

            rs = ps.executeQuery();
            while (rs.next()){
                u = new USER(
                        rs.getString("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_pass"),
                        rs.getString("user_sex"),
                        rs.getString("user_birthday1"),
                        rs.getString("user_idenity_code"),
                        rs.getString("user_email"),
                        rs.getString("user_mobile"),
                        rs.getString("user_address"),
                        rs.getInt("user_status")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return u;
    }
    public static USER selectAdmin(String name,String pwd){
        USER u = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        //声明结果集
        ResultSet rs = null;

        PreparedStatement ps = null;
        try{
            String sql = "select m.*, DATE_FORMAT(m.user_birthday,'%Y-%m-%d') user_birthday1 from lmonkeyshop m where user_id = ? and user_pass = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setObject(2,pwd);

            rs = ps.executeQuery();
            while (rs.next()){
                u = new USER(
                        rs.getString("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_pass"),
                        rs.getString("user_sex"),
                        rs.getString("user_birthday1"),
                        rs.getString("user_idenity_code"),
                        rs.getString("user_email"),
                        rs.getString("user_mobile"),
                        rs.getString("user_address"),
                        rs.getInt("user_status")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return u;
    }

    public static USER selectByeamil(String id){
        USER u = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        //声明结果集
        ResultSet rs = null;

        PreparedStatement ps = null;
        try{
            String sql = "select * from lmonkeyshop m where user_id = ? or user_email = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            ps.setObject(2,id);

            rs = ps.executeQuery();
            while (rs.next()){
                u = new USER(
                        rs.getString("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_pass"),
                        rs.getString("user_sex"),
                        rs.getString("user_birthday"),
                        rs.getString("user_idenity_code"),
                        rs.getString("user_email"),
                        rs.getString("user_mobile"),
                        rs.getString("user_address"),
                        rs.getInt("user_status")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return u;
    }

    public static int updateMima(String mima,String name){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String pass1 = MD5.md5(mima);
            String sql = "update lmonkeyshop set user_pass = ? where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,pass1);
            ps.setObject(2,name);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int updateDate(String yname,String name,String birthday,String sex){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkeyshop set user_id = ? ,user_birthday=DATE_FORMAT(?,'%Y-%m-%d'),user_sex = ? where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,birthday);
            ps.setObject(3,sex);
            ps.setObject(4,yname);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int inserPhoto(String filename,String username){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "insert into lmonkey_photo values (? ,?) ";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,username);
            ps.setObject(2,filename);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    public static int updatePhoto(String filename,String name){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_photo set user_filename = ? where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,filename);
            ps.setObject(2,name);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public  static PHOTO select(String name){
        PHOTO p = null;

        Connection conn = Basedao.getconn();
        PreparedStatement ps =null;
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_photo where user_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            rs = ps.executeQuery();
            while(rs.next()){
                 p = new PHOTO(
                        rs.getString("user_id"),
                        rs.getString("user_filename")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

    public static int selectLogin(String name,String pass){
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        //在数据库中查询数据
        try {
            String pass1 = MD5.md5(pass);
            String sql = "select count(*) from lmonkeyshop where user_id=? and user_pass = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,pass1);
            rs = ps.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public static int updateAddress(String name,String id,String p,String as,int fl,String name1){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_address set user_name = ? ,user_mobile = ? ,user_address = ? where user_name = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,p);
            ps.setObject(3,as);
            ps.setObject(4,name1);
            count = ps.executeUpdate();
            if (fl == 1){
                String sql1 = "update lmonkeyshop set user_name = ? ,user_mobile = ? ,user_address = ? where user_id = ?";
                ps = conn.prepareStatement(sql1);
                ps.setObject(1,name);
                ps.setObject(2,p);
                ps.setObject(3,as);
                ps.setObject(4,id);
                count = ps.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int select1ById(String id,String name){
       int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        //声明结果集
        ResultSet rs = null;

        PreparedStatement ps = null;
        try{
            String sql = "select count(*)  from lmonkeyshop  where user_id = ? and user_name= ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            ps.setString(2,name);

            rs = ps.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

}
